package com.muk.meis.model.service.report;

import java.io.StringWriter;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.Iterator;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Text;

import com.muk.meis.MEISConstance.SCALE;
import com.muk.meis.model.bean.School;
import com.muk.meis.model.service.BaseMEISService;

public class ReportByDistrict extends BaseMEISService {
	
	public List<School> getReportByDistrict(SCALE scale,String district){
		String sql = "select "+
				 " sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+a.primaryEducationMan1+"+
				 "	a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+a.primaryEducationMan5+a.primaryEducationMan6+"+
				 "	a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6+"+
				 "	a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"+
				 "	a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+a.primaryEducationGirl5+a.primaryEducationGirl6+"+
				 "	a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6),"+
				 "  a "+
				 "	from School a, SchoolInfo b"+ 
				 "	where a.percodeCode = b.percodeCode"+
				 "	      and b.districtId = "+district+
				 "	group by b" +
				 "	having sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+a.primaryEducationMan1+"+
				 "	a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+a.primaryEducationMan5+a.primaryEducationMan6+"+
				 "	a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6+"+
				 "	a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"+
				 "	a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+a.primaryEducationGirl5+a.primaryEducationGirl6+"+
				 "	a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6) %S"+				 
				 "  order by 1 asc";
		sql = templateSQLScale(scale, sql);
		System.out.println(sql);
		List lst = em.createQuery(sql).getResultList();
		List<School> retLst = new ArrayList<School>();
		for (Iterator iterator = lst.iterator(); iterator.hasNext();) {
			Object[] object = (Object[]) iterator.next();
			retLst.add((School)object[1]);
		}
		return retLst;
	}
	
	public String getXMLReportByDistrict(String district) {
		String sqlTemp = "select a.percodeCode,"
				+ " sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+"
				+ "    a.primaryEducationMan1+a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+"
				+ "    a.primaryEducationMan5+a.primaryEducationMan6+"
				+ "    a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6+"
				+ "    a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"
				+ "    a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+"
				+ "    a.primaryEducationGirl5+a.primaryEducationGirl6+"
				+ "    a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6)"
				+ " from School a, SchoolInfo b"
				+ " where a.percodeCode = b.percodeCode"
				+"	      and b.districtId = "+district
				+ " group by 1"
				+ " having  sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+"
				+ "         a.primaryEducationMan1+a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+"
				+ "         a.primaryEducationMan5+a.primaryEducationMan6+"
				+ "         a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6+"
				+ "         a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"
				+ "         a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+"
				+ "         a.primaryEducationGirl5+a.primaryEducationGirl6+"
				+ "         a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6) %s";
		try {
			// ///////////////////////////
			// Creating an empty XML Document

			// We need a Document
			DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
			DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
			Document doc = docBuilder.newDocument();

			// //////////////////////
			// Creating the XML tree

			// create the root element and add it to the document
			Element root = doc.createElement("pie");
			doc.appendChild(root);

			// create child element, add an attribute, and add to root

			for (SCALE scale : EnumSet.allOf(SCALE.class)) {
				if (scale.isSubScale())
					continue;
				String sql = templateSQLScale(scale, sqlTemp);
				List<Object[]> lst = em.createQuery(sql).getResultList();
				Element child = doc.createElement("slice");
				child.setAttribute("title", scale.getMessageDisplay());
				Text text = doc.createTextNode(sum(lst) + "");
				child.appendChild(text);
				root.appendChild(child);
			}

			// ///////////////
			// Output the XML

			// set up a transformer
			TransformerFactory transfac = TransformerFactory.newInstance();
			Transformer trans = transfac.newTransformer();

			// create string from xml tree
			StringWriter sw = new StringWriter();
			StreamResult result = new StreamResult(sw);
			DOMSource source = new DOMSource(doc);
			trans.transform(source, result);
			String xmlString = sw.toString();

			return xmlString.substring(xmlString.indexOf("?>") + 2).replaceAll(
					"\"", "'");
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}
	
	private Integer sum(List<Object[]> lst){
		Integer ret = 0;
		for (Object[] objects : lst) {
			ret += Integer.parseInt(objects[1].toString());
		}
		return ret;
	}
	
	public static void main(String[] args) {
		ReportByDistrict obj = new ReportByDistrict();
		System.out.println(obj.getXMLReportByDistrict("1"));
	}

}
